package com.bms.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.bms.entity.User;

public class UserDao {

	/**
	 * 保存用户信息
	 * 
	 * @param temp
	 *            需要存储的用户的信息
	 */
	public void insert(User temp) {
		String url = "jdbc:mysql://localhost:3306/books";// 数据库地址
		String user = "fangzuchang";// 数据库登录名
		String password = "13920778329";
		Connection con = null;// 数据库链接对象
		PreparedStatement statement = null;
		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection(url, user, password);

			String sql = "insert into users(userName,realName,password) values(?,?,?)";
			statement = con.prepareStatement(sql);

			// 绑定数据
			statement.setString(1, temp.getUserName());
			statement.setString(2, temp.getRealName());
			statement.setString(3, temp.getPassword());

			statement.execute();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (statement != null) {
					statement.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 查询users表中的所有用户的信息
	 * 
	 * @return
	 */
	public List<User> queryAll() {
		String url = "jdbc:mysql://localhost:3306/books";// 数据库地址
		String user = "fangzuchang";// 数据库登录名
		String password = "13920778329";
		Connection con = null;// 数据库链接对象
		Statement statement = null;
		ResultSet resultSet = null;

		// 大篮子
		List<User> users = new ArrayList<User>();
		User temp = null;

		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection(url, user, password);
			statement = con.createStatement();
			String sql = "select * from users";
			resultSet = statement.executeQuery(sql);
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String userName = resultSet.getString("userName");
				String realName = resultSet.getString("realName");
				String pwd = resultSet.getString("password");

				temp = new User(id, userName, realName, pwd);
				users.add(temp);
				temp = null;
			}
			return users;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}
				if (statement != null) {
					statement.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	/**
	 * 根据id删除用户的信息
	 * 
	 * @param id
	 */
	public void deleteUserById(int id) {
		String url = "jdbc:mysql://localhost:3306/books";// 数据库地址
		String user = "fangzuchang";// 数据库登录名
		String password = "13920778329";
		Connection con = null;// 数据库链接对象
		PreparedStatement statement = null;
		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection(url, user, password);
			String sql = "delete from users where id=?";
			statement = con.prepareStatement(sql);
			// 绑定数据
			statement.setInt(1, id);
			statement.execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (statement != null) {
					statement.close();
				}
				if (con != null) {
					statement.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 根据用户名、密码获取用户的信息
	 * 
	 * @param param
	 * @return
	 */
	public User queryUser(User param) {
		String url = "jdbc:mysql://localhost:3306/books";// 数据库地址
		String user = "fangzuchang";// 数据库登录名
		String password = "13920778329";
		Connection con = null;// 数据库链接对象
		PreparedStatement statement = null;
		ResultSet resultSet = null;

		User temp = null;

		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection(url, user, password);
			String sql = "select * from users where userName=? and password=?";
			statement = con.prepareStatement(sql);

			// 绑定数据
			statement.setString(1, param.getUserName());
			statement.setString(2, param.getPassword());

			resultSet = statement.executeQuery();

			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String userName = resultSet.getString("userName");
				String realName = resultSet.getString("realName");
				String pwd = resultSet.getString("password");

				temp = new User(id, userName, realName, pwd);
			}
			return temp;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}
				if (statement != null) {
					statement.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	/**
	 * 通过id查询用户的信息
	 * 
	 * @param param
	 *            用户的id
	 * @return
	 */
	public User getUserInfoById(int param) {
		String url = "jdbc:mysql://localhost:3306/books";// 数据库地址
		String user = "fangzuchang";// 数据库登录名
		String password = "13920778329";
		Connection con = null;// 数据库链接对象
		PreparedStatement statement = null;
		ResultSet resultSet = null;

		User temp = null;

		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection(url, user, password);
			String sql = "select * from users where id=?";
			statement = con.prepareStatement(sql);

			// 绑定数据
			statement.setInt(1, param);

			resultSet = statement.executeQuery();

			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String userName = resultSet.getString("userName");
				String realName = resultSet.getString("realName");
				String pwd = resultSet.getString("password");

				temp = new User(id, userName, realName, pwd);
			}
			return temp;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}
				if (statement != null) {
					statement.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	/**
	 * 更新用户的信息
	 * 
	 * @param papram
	 */
	public void updateUser(User papram) {
		System.out.println("开始更新用户信息");
		String url = "jdbc:mysql://localhost:3306/books";// 数据库地址
		String user = "fangzuchang";// 数据库登录名
		String password = "13920778329";
		Connection con = null;// 数据库链接对象
		PreparedStatement statement = null;
		System.out.println(papram.getPassword());
		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection(url, user, password);
			String sql = "update users set realName=?,password=? where id=?";
			statement = con.prepareStatement(sql);
			// 绑定数据
			statement.setString(1, papram.getRealName());
			statement.setString(2, papram.getPassword());
			statement.setInt(3, papram.getId());
			statement.execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (statement != null) {
					statement.close();
				}
				if (con != null) {
					statement.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

}
